﻿//using EPPlus.Extensions;
using OfficeOpenXml;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;

namespace CommonUtils
{
    /// <summary>
    /// 使用  EPPlus 第三方的组件读取Excel
    /// </summary>
    public class EPPlusHelper
    {
        private static string GetString(object obj)
        {

            if (obj == null)
                return "";

            return obj.ToString();

        }

        /// <summary>
        ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）
        /// </summary>
        /// <param name="fullFielPath">文件的绝对路径</param>
        /// <returns></returns>
        public static DataTable WorksheetToTable(string fullFielPath, string sheetName = null)
        {
            //如果是“EPPlus”，需要指定LicenseContext。
            //EPPlus.Core 不需要指定。
            //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            FileInfo existingFile = new FileInfo(fullFielPath);

            ExcelPackage package = new ExcelPackage(existingFile);
            ExcelWorksheet worksheet = null;

            if (string.IsNullOrEmpty(sheetName))
            {
                //不传入 sheetName 默认取第1个sheet。
                //EPPlus 索引是0
                //EPPlus.Core 索引是1
                worksheet = package.Workbook.Worksheets[1];
            }
            else
            {
                worksheet = package.Workbook.Worksheets[sheetName];
            }

            if (worksheet == null)
                throw new Exception("指定的sheetName不存在");

            return WorksheetToTable(worksheet);
        }

        /// <summary>
        /// 将worksheet转成datatable
        /// </summary>
        /// <param name="worksheet">待处理的worksheet</param>
        /// <returns>返回处理后的datatable</returns>
        public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
        {
            //获取worksheet的行数
            int rows = worksheet.Dimension.End.Row;
            //获取worksheet的列数
            int cols = worksheet.Dimension.End.Column;

            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;
            for (int i = 1; i <= rows; i++)
            {
                if (i > 1)
                    dr = dt.Rows.Add();

                for (int j = 1; j <= cols; j++)
                {
                    //默认将第一行设置为datatable的标题
                    if (i == 1)
                        dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
                    //剩下的写入datatable
                    else
                        dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
                }
            }
            return dt;
        }


        public static void TestEPPlus()
        {
            string saa = @"D:\2023.xlsx";
            string order = @"D:\2023order.xlsx";
            string sourceFile = @"D:\sourceFile.xlsx";
            string targetFile = @"D:\targetFile.xlsx";

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//指明非商业应用
            ExcelPackage packageName = new ExcelPackage(saa);//加载Excel工作簿
            ExcelWorksheet sheetSaaName = packageName.Workbook.Worksheets["Sheet1"];//读取工作簿中名为"Sheet1"的工作表


            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//指明非商业应用
            ExcelPackage packageOrder = new ExcelPackage(order);//加载Excel工作簿
            ExcelWorksheet sheetSaaOrder = packageOrder.Workbook.Worksheets["Sheet1"];//读取工作簿中名为"Sheet1"的工作表


            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//指明非商业应用
            ExcelPackage package = new ExcelPackage(sourceFile);//加载Excel工作簿
            ExcelWorksheet sheet1 = package.Workbook.Worksheets["Sheet1"];//读取工作簿中名为"Sheet1"的工作表

            List<string> names = new List<string>();

            for (int i = 0; i < 10000; i++)
            {
                var tempVal = sheetSaaName.Cells["C" + i].Value;

                if (tempVal != null && !string.IsNullOrWhiteSpace(tempVal.ToString())) names.Add(tempVal.ToString().Trim().Replace(" ", ""));
            }


            List<string> orders = new List<string>();
            for (int i = 0; i < 10000; i++)
            {

                var tempVal = sheetSaaOrder.Cells["G" + i].Value;
                if (tempVal != null && !string.IsNullOrWhiteSpace(tempVal.ToString()))
                {
                    tempVal = tempVal.ToString().Trim().Replace(" ", "");

                    var arrList = tempVal.ToString().Split("、").ToList();

                    var cell = sheet1.Cells[i + 1, 1];
                    cell.IsRichText = true;

                    for (int a = 0; a < arrList.Count; a++)
                    {

                        var x = arrList[a];
                        if (names.Contains(x))
                        {
                            var part1 = cell.RichText.Add(x);
                            part1.Color = System.Drawing.Color.Red;
                        }
                        else
                        {
                            var part2 = cell.RichText.Add(x);
                            part2.Color = System.Drawing.Color.Black;
                        }

                        if (a != arrList.Count - 1)
                        {
                            var part3 = cell.RichText.Add("、");
                            part3.Color = System.Drawing.Color.Black;
                        }
                    }

                    //var cell = sheet1.Cells[i + 1, 1];
                    //cell.IsRichText = true;
                    ////Create the content
                    //var part1 = cell.RichText.Add("This is blue bold");
                    //part1.Bold = true;
                    //part1.Color = System.Drawing.Color.Blue;

                }
            }




            //var cell = sheet1.Cells[1, 1];
            //cell.IsRichText = true;
            ////Create the content
            //var part1 = cell.RichText.Add("This is blue bold");
            //part1.Bold = true;
            //part1.Color = System.Drawing.Color.Blue;
            //var part2 = cell.RichText.Add(" And this is red and not bold");
            //part2.Bold = false;
            //part2.Color = System.Drawing.Color.Red;

            //sheet1.Cells[3, 2, 3, 3].Merge = true;
            //package.Save();//将更改保存到原文件
            package.SaveAs(targetFile);//将更改保存到新的文件，类似于另存为
        }
    }


}